<?php
   
    /**
    * server.class.php
    * Esta clase es responsable de gestionar la capa de datos respondiendo
    * a las peticiones relacionadas con los comandos de la API.
    * @author Sebastian Lara <slara@grvertice.com>
    * @version 1.0
    * @created 20/DEC/2012
    * @copyright
    */

    class Server {
        private $cliCode;
        private $cliEmail;
        private $cliCredit;
        private $cliCurrency;
        private $cliLanguage;
        private $apiKey;
        private $columnPrice;

        const NO_DATA_FOUND  = 'NO_DATA_FOUND';
        const INTERNAL_ERROR = 'INTERNAL_ERROR';
        const NOT_SUPPORTED  = 'NOT_SUPPORTED';


        /**
        * Función constructora de la clase
        * @param none
        * @return none
        * @created 26/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        function __construct() {
            $this->cliCode     = NULL;
            $this->cliEmail    = NULL;
            $this->cliCredit   = NULL;
            $this->cliCurrency = NULL;
            $this->apiKey      = NULL;
            $this->cliLanguage = NULL;
            $this->columnPrice = NULL;
        }


        /**
        * Función responsable de validar si un API KEY es o nó válida.
        * @param string $apiKey, llave a validar.
        * @return boolean, resultado de la validación
        * @created 26/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function validateApiKey($apiKeyMU, $cliCode, $secretNumber) {
            $query = "SELECT c.cli_code, c.cli_email, c.cli_bonus, c.cli_payment_form, "
                   . "c.cli_language_email "
                   . "FROM client c "
                   . "WHERE c.cli_code="
                   . "(SELECT DISTINCT ak.cli_code FROM api_key_masterunlock ak "
                   . "WHERE ak.apikeymu_value='" . $apiKeyMU . "' "
                   . "AND ak.apikeymu_status=1 AND ak.apikeymu_current=1) "
                   . "AND c.cli_code=" . $cliCode . " "
                   . "AND (SELECT count(*) FROM api_key_masterunlock ak "
                   . "WHERE ak.cli_code=" . $cliCode . ")=" . $secretNumber.";";
            $response = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    $response = TRUE;

                    $this->cliCode     = $row['cli_code'];
                    $this->cliEmail    = $row['cli_email'];
                    $this->cliCredit   = $row['cli_bonus'];
                    $this->cliCurrency = $row['cli_payment_form'];
                    $this->cliLanguage = $row['cli_language_email'];
                    $this->apiKey      = $apiKeyMU;

                    switch ($this->cliCurrency) {
                        case 'Pesos Colombianos':
                            $this->columnPrice = 'clpr_price_col';
                            break;
                        
                        case 'Dolares':
                            $this->columnPrice = 'clpr_price_us';
                        break;
                        
                        case 'Bolivares':
                            $this->columnPrice = 'clpr_price_bol';
                        break;
                        
                        default:
                            // el cliente debe contar con una moneda de pago
                            $response = self :: INTERNAL_ERROR;
                        break;
                    }
                }
            } else {
                $response = self :: INTERNAL_ERROR;
            }

            return $response;
        }

        // <commands>

        /**
        * Función responsable de obtener la información de la cuenta master-unlock
        * correspondiente a la API KEY que llega como parámetro.
        * @param string $apiKey, llave usada para consultar la información
        * de una cuenta.
        * @return array $data, datos de la cuenta correspondientes al API KEY
        * @created 26/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function getAccountInfo() {
            $query  = "SELECT c.cli_code, c.cli_email, c.cli_bonus, "
                    . "c.cli_payment_form, c.cli_language_email "
                    . "FROM client c "
                    . "WHERE c.cli_code = " . $this->cliCode . ";";
            $responseList = NULL;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)){
                    $responseList[0]['EMAIL']    = $row['cli_email'];
                    $responseList[0]['CREDITS']  = number_format($row['cli_bonus'], 2);
                    $responseList[0]['CURRENCY'] = $this->getCorrespondingValue('VALUES_CURRENCY', $row['cli_payment_form']);
                    $responseList[0]['LANGUAGE'] = $this->getCorrespondingValue('VALUES_LANGUAGE', $row['cli_language_email']);
                } else {
                    $responseList = self :: NO_DATA_FOUND;
                }

            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            return $responseList;
        }


        /**
        * Función responsable de obtener el listado de los modelos soportados por
        * master-unlock agrupados por marca.
        * @param string $brandCode, (opcional) código de una marca específica, 
        * de la cuál se quieren obtener sus modelos asociados.
        * @return array, listado de modelos
        * @created 27/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function getMobiles($brandCode = NULL) {
            $query  = "SELECT b.bra_code, b.bra_name, m.mod_code, m.mod_name "
                    . "FROM brand b "
                    . "JOIN model m ON b.bra_code=m.bra_code ";
            
            if ((int) $brandCode > 0) {
                $query .= "WHERE b.bra_code=" . (int) $brandCode . " ";
            }

            $query .= "ORDER BY b.bra_code, m.mod_code;"; // limit
            $responseList = NULL;

            if ($result = mysql_query ($query)) {
                $braCode = NULL;
                $braName = NULL;
                $j = 0;

                while ($row = mysql_fetch_array ($result)) {
                    if ($braCode != $row['bra_code']) {
                        $braCode = $row['bra_code'];
                        $braName = $row['bra_name'];
                        
                        $responseList[$braCode]['TAG']    = 'BRAND';
                        $responseList[$braCode]['ID']     = $braCode;
                        $responseList[$braCode]['NAME']   = $braName;
                        $responseList[$braCode]['CHILDS'] = array ();

                        $j = 0;
                    }

                    $responseList[$braCode]['CHILDS'][$j] = array (
                        'TAG'  => 'MOBILE',
                        'ID'   => $row['mod_code'],
                        'NAME' => $row['mod_name'],
                    );

                    $j++;
                    
                }
            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            if (empty ($responseList)) {
                $responseList = self :: NO_DATA_FOUND;
            }

            return $responseList;
        }


        /**
        * Función responsable de obtener el listado de las redes soportados por
        * master-unlock agrupados por país.
        * @param string $countryCode, (opcional) código de un país específico, 
        * del cuál se quieren obtener sus modelos asociados.
        * @return array, listado de redes
        * @created 27/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function getNetworks($countryCode = NULL) {
            $query  = "SELECT c.cou_code, c.cou_name, r.red_id, r.red_name "
                    . "FROM country c "
                    . "JOIN red r ON c.cou_code=r.cou_code ";
            
            if ((int) $countryCode > 0) {
                $query .= "WHERE c.cou_code=" . (int) $countryCode . " ";
            }

            $query .= "ORDER BY c.cou_code, r.red_id;";// limit 194, 316

                    //1005
            $responseList = NULL;

            if ($result = mysql_query ($query)) {
                $couCode = NULL;
                $couName = NULL;
                $j = 0;

                while ($row = mysql_fetch_array ($result)) {
                    if ($couCode != $row['cou_code']) {
                        $couCode = $row['cou_code'];
                        $couName = $row['cou_name'];
                        
                        $responseList[$couCode]['TAG']    = 'COUNTRY';
                        $responseList[$couCode]['ID']     = $couCode;
                        $responseList[$couCode]['NAME']   = $couName;
                        $responseList[$couCode]['CHILDS'] = array ();

                        $j = 0;
                    }

                    $responseList[$couCode]['CHILDS'][$j] = array (
                        'TAG'  => 'NETWORK',
                        'ID'   => $row['red_id'],
                        'NAME' => $row['red_name'],
                    );

                    $j++;
                    
                }
            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            if (empty ($responseList)) {
                $responseList = self :: NO_DATA_FOUND;
            }

            return $responseList;
        }


        /**
        * Función responsable de obtener el listado de los servicios soportados
        * por master-unlock ordenados por grupo.
        * @param string $groupCode, (opcional) código de un grupo específico, 
        * del cuál se quieren obtener sus servicios asociados.
        * @return array, listado de servicios
        * @created 27/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function getServices($groupCode = NULL) {

            $query  = "SELECT t1.ser_type_code, t1.ser_type_name, t1.type_service, t1.ser_code, "
                    . "t1.ser_name, t1.ser_status, t1.ser_time_response, t1.ser_req_country, "
                    . "t1.ser_req_operator, t1.ser_req_models, t1.ser_req_MEP, t1.ser_req_id, "
                    . "t1.ser_req_serie, t1.ser_req_user,  t1.ser_req_idbox, t1.ser_req_nick, "
                    . "t1.ser_req_pass, t1.ser_req_rm, t1.req_all_brand, "
                    . "t2." . $this->columnPrice . " "

                    . "FROM "

                    . "(SELECT st.ser_type_code, st.ser_type_name, st.type_service, s.ser_code, "
                    . "s.ser_name, s.ser_status, s.ser_time_response, s.ser_req_country, "
                    . "s.ser_req_operator, s.ser_req_models, s.ser_req_MEP, s.ser_req_id, "
                    . "s.ser_req_serie, s.ser_req_user,  s.ser_req_idbox, s.ser_req_nick, "
                    . "s.ser_req_pass, s.ser_req_rm, s.req_all_brand "
                    . "FROM (SELECT st.ser_type_code, st.ser_type_name, st.type_service FROM service_type st "
                    . "WHERE st.type_service='Imei' OR st.type_service='Cable') st JOIN services s "
                    . "ON st.ser_type_code=s.ser_type_code) t1 "

                    . "JOIN "

                    . "(SELECT cp.ser_code, cp." . $this->columnPrice . " "
                    . "FROM client_price cp WHERE cp.cli_code=" . $this->cliCode . ") t2 "

                    . "ON t1.ser_code=t2.ser_code "


                    . "ORDER BY t1.ser_type_code, t1.ser_code LIMIT 0, 66";

            $responseList = NULL;

            if ($result = mysql_query ($query)) {
                $groupCode = NULL;
                $groupName = NULL;

                $j = 0;

                while ($row = mysql_fetch_array ($result)) {

                    if ($groupCode != $row['ser_type_code']) {
                        $groupCode = $row['ser_type_code'];
                        $groupName = $row['ser_type_name'];
                        
                        $responseList[$groupCode]['TAG']    = 'GROUP';
                        $responseList[$groupCode]['ID']     = $groupCode;
                        $responseList[$groupCode]['NAME']   = $groupName;
                        $responseList[$groupCode]['CHILDS'] = array ();

                        $j = 0;
                    }

                    $responseList[$groupCode]['CHILDS'][$j] = array (
                        'TAG'                => 'SERVICE',
                        'ID'                 => $row['ser_code'],
                        'NAME'               => ($row['ser_name']),
                        'STATUS'             => $this->getCorrespondingValue('VALUES_STATUS', $row['ser_status']),
                        'CREDITS'            => $row[$this->columnPrice],
                        'SERVICE_TYPE'       => $row['type_service'],
                        'TIME_RESPONSE'      => $row['ser_time_response'],
                        'REQUIRE_COUNTRY'    => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_country']),
                        'REQUIRE_NETWORK'    => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_operator']),
                        'REQUIRE_MODEL'      => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_models']),
                        'REQUIRE_MEP'        => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_MEP']),
                        'REQUIRE_PROVIDER'   => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_id']),
                        'REQUIRE_SERIAL'     => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_serie']),
                        'REQUIRE_USER'       => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_user']),
                        'REQUIRE_ID_BOX'     => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_idbox']),
                        'REQUIRE_NICK'       => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_nick']),
                        'REQUIRE_PASSWORD'   => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_pass']),
                        'REQUIRE_RM'         => $this->getCorrespondingValue('VALUES_REQUIRED', $row['ser_req_rm']),
                        'REQUIRE_ALL_MODELS' => $row['req_all_brand'] ? 'TRUE' : 'FALSE',
                    );

                    $j++;
                    
                }
            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            if (empty ($responseList)) {
                $responseList = self :: NO_DATA_FOUND;
            }

            return $responseList;
        }


        /**
        * Función responsable obtener el listado de modelos soportados por un
        * servicio master-unlock en particular.
        * @param string $serviceCode, código de un grupo específico del cuál
        * se quieren obtener los modelos asociados.
        * @return array, listado de modelos.
        * @created 27/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function getServiceMobiles($serviceCode = NULL) {
            $query  = "SELECT m.mod_code, m.mod_name "
                    . "FROM services_model sm JOIN model m "
                    . "ON sm.mod_code = m.mod_code "
                    . "WHERE sm.ser_code=" . (int) $serviceCode ." "
                    . "ORDER BY m.mod_code limit 0, 2;";

            $responseList = NULL;

            if ($result = mysql_query ($query)) {
                while ($row = mysql_fetch_array ($result)) {
                    $mobileCode = $row['mod_code'];
                    $mobileName = $row['mod_name'];
                    
                    $responseList[$mobileCode]['TAG']  = 'MOBILE';
                    $responseList[$mobileCode]['ID']   = $mobileCode;
                    $responseList[$mobileCode]['NAME'] = $mobileName;
                }
            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            if (empty ($responseList)) {
                $responseList = self :: NO_DATA_FOUND;
            }

            return $responseList;
        }

        
        /**
        * Devuelve una lista de los códigos de desbloqueo ordenados en una 
        * cuenta master-unlock. Por defecto, devuelve todos los pedidos si
        * no tiene un parámetro enviado.
        * @param string $serviceCode, código de un grupo específico del cuál
        * se quieren obtener las redes asociadas.
        * @return array, listado de redes.
        * @created 27/DEC/2012
        * @author Sebastián Lara <slara@grverice.com>
        */
        public function getOrders($orderStatus = NULL, $serviceCode = NULL,
                $imei = NULL, $orderId = NULL, $dateFrom = NULL, $dateTo = NULL, 
                $deliveryFrom = NULL, $deliveryTo = NULL) {

            $responseList = NULL;
            $where = '';
            $whereServices = '';

            if (! empty ($orderStatus)) {
                $where = 'WHERE li.libimei_status=' . $orderStatus . " ";
            }

            if (! empty ($serviceCode)) {
                $whereServices = ' WHERE s.ser_code=' . (int) $serviceCode . " ";
            }

            if (! empty ($imei)) {
                if (empty ($where)) {
                    $where = "WHERE li.libimei_number=" . $imei . " ";
                } else {
                    $where .= " AND li.libimei_number=" . $imei . " ";
                }
            }
            
            if (! empty ($orderId)) {
                if (empty ($where)) {
                    $where = "WHERE li.libimei_code=" . $orderId . " ";
                } else {
                    $where .= " AND li.libimei_code=" . $orderId . " ";
                }
            }

            if (! empty ($dateFrom)) {
                if (empty ($where)) {
                    $where = "WHERE DATE(li.libimei_date)>='" . $dateFrom . "' ";
                } else {
                    $where .= " AND DATE(li.libimei_date)>='" . $dateFrom . "' ";
                }
            }

            if (! empty ($dateTo)) {
                if (empty ($where)) {
                    $where = "WHERE DATE(li.libimei_date)<='" . $dateTo . "' ";
                } else {
                    $where .= " AND DATE(li.libimei_date)<='" . $dateTo . "' ";
                }
            }

            if (! empty ($deliveryFrom)) {
                if (empty ($where)) {
                    $where = "WHERE DATE(li.libimei_date_liberation)>='" . $deliveryFrom . "' ";
                } else {
                    $where .= " AND DATE(li.libimei_date_liberation)>='" . $deliveryFrom . "' ";
                }
            }

            if (! empty ($deliveryTo)) {
                if (empty ($where)) {
                    $where = "WHERE DATE(li.libimei_date_liberation)<='" . $deliveryTo . "' ";
                } else {
                    $where .= " AND DATE(li.libimei_date_liberation)<='" . $deliveryTo . "' ";
                }
            }

            $query = "SELECT l.ser_code, l.ser_name, l.type_service, l.lib_country, l.lib_operator, "
                   . "l.lib_model, l.lib_numMep, l.lib_id, l.lib_serie, l.lib_user, "
                   . "l.lib_id_box, l.lib_nick, l.lib_pass, l.lib_rm, "
                   . "li.libimei_code, li.libimei_number, li.libimei_date, "
                   . "li.libimei_date_liberation, li.libimei_number_liberation, "
                   . "li.libimei_status, li.libimei_bonus, li.libimei_verification "

                   . "FROM "

                   . "(SELECT s.ser_code, s.ser_name, s.type_service, l.lib_code, l.lib_country, "
                   . "l.lib_operator, l.lib_model, l.lib_numMep, l.lib_id, l.lib_serie, "
                   . "l.lib_user, l.lib_id_box, l.lib_nick, l.lib_pass, l.lib_rm "
                   . "FROM liberations l JOIN "

                   . "(SELECT s.ser_code, s.ser_name, st.type_service FROM services s "
                   . "JOIN (SELECT st.ser_type_code, st.type_service FROM service_type st "
                   . "WHERE st.type_service='Imei' OR st.type_service='Cable') st "
                   . "ON s.ser_type_code=st.ser_type_code " . $whereServices . ") s "

                   . "ON l.ser_code=s.ser_code "
                   . "WHERE l.cli_code=". (int) $this->cliCode . ") l "
                   
                   . "JOIN liberations_imei li "
                   . "ON l.lib_code=li.lib_code "
                   . $where
                   . "ORDER BY li.libimei_code DESC "
                   . "LIMIT 0, 10;";

            if ($result = mysql_query ($query)) {
                while ($row = mysql_fetch_array ($result)) {
                    $orderId = $row['libimei_code'];

                    $responseList[$orderId]['TAG']          = 'ORDER';
                    $responseList[$orderId]['ID']           = $row['libimei_code'];
                    $responseList[$orderId]['DATE']         = $row['libimei_date'];
                    $responseList[$orderId]['IMEI']         = $row['libimei_number'];
                    $responseList[$orderId]['CREDITS']      = number_format($row['libimei_bonus'], 2);
                    $responseList[$orderId]['SERVICE_ID']   = $row['ser_code'];
                    $responseList[$orderId]['SERVICE_NAME'] = $row['ser_name'];
                    $responseList[$orderId]['SERVICE_TYPE'] = $row['type_service'];                    
                    $responseList[$orderId]['STATUS']       = $this->getCorrespondingValue('VALUES_UNLOCK', $row['libimei_status']);
                    $responseList[$orderId]['DELIVERY']     = $row['libimei_date_liberation'];
                    $responseList[$orderId]['CODES']        = $row['libimei_number_liberation'];
                    $responseList[$orderId]['VERIFIABLE']   = $this->getCorrespondingValue('VALUES_VERIFICATION', $row['libimei_verification']);
                    $responseList[$orderId]['COUNTRY_ID']   = $this->getCorrespondingValue(NULL, $row['lib_country']);
                    $responseList[$orderId]['RED_ID']       = $this->getCorrespondingValue(NULL, $row['lib_operator']);
                    $responseList[$orderId]['MOBILE_ID']    = $this->getCorrespondingValue(NULL, $row['lib_model']);
                    $responseList[$orderId]['MEP']          = $this->getCorrespondingValue(NULL, $row['lib_numMep']);
                    $responseList[$orderId]['PROVIDER']     = $this->getCorrespondingValue(NULL, $row['lib_id']);
                    $responseList[$orderId]['SERIAL']       = $this->getCorrespondingValue(NULL, $row['lib_serie']);
                    $responseList[$orderId]['USER']         = $this->getCorrespondingValue(NULL, $row['lib_user']);
                    $responseList[$orderId]['ID_BOX']       = $this->getCorrespondingValue(NULL, $row['lib_id_box']);
                    $responseList[$orderId]['NICK']         = $this->getCorrespondingValue(NULL, $row['lib_nick']);
                    $responseList[$orderId]['PASSWORD']     = $this->getCorrespondingValue(NULL, $row['lib_pass']);
                    $responseList[$orderId]['RM']           = $this->getCorrespondingValue(NULL, $row['lib_rm']);
                }
            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            if (empty ($responseList)) {
                $responseList = self :: NO_DATA_FOUND;
            }

            return $responseList;
        }


        // </commands>

        // <utilities>

        /**
        * Función responsable de obtener un valor correspondiente, teniendo en cuenta
        * la categoría que se especifíca como parámetro.
        * @param string $value, valor entero que se quiere obtener el significado 
        * asociado
        * @return string $stringValue, cadena correspondiente al significado del
        * entero que llega como parámetro.
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function getCorrespondingValue($category = NULL, $value = NULL) {
            $strinValue = '';

            switch ($category) {
                case 'VALUES_REQUIRED':
                    
                    switch ($value) {
                        case '0':
                            $strinValue = 'NONE';
                            break;
                        case '1':
                            $strinValue = 'OPTIONAL';
                        break;case '2':
                            $strinValue = 'REQUIRED';
                            break;
                        
                        default:
                            $strinValue = 'NONE';
                            break;
                    }
                    break;
                
                case 'VALUES_STATUS':
                    switch ($value) {
                        case '0':
                            $strinValue = 'INACTIVE';
                            break;
                        case '1':
                            $strinValue = 'ACTIVE';
                        break;

                        default:
                            $strinValue = 'NONE';
                            break;
                    }
                    break;

                
                 case 'VALUES_UNLOCK':
                    switch ($value) {
                        case '0':
                            $strinValue = 'WAITING';
                            break;
                        case '1':
                            $strinValue = 'DELIVERED';
                        break;
                        case '2':
                            $strinValue = 'VERIFICATION';
                            break;
                        case '3':
                            $strinValue = 'NOT_IN_DATABASE';
                        break;

                        default:
                            $strinValue = 'NONE';
                            break;
                    }
                    break;

                case 'VALUES_UNLOCK_REVERSE':
                    switch ($value) {
                        case 'WAITING':
                            $strinValue = '0';
                            break;
                        case 'DELIVERED':
                            $strinValue = '1';
                        break;
                        case 'VERIFICATION':
                            $strinValue = '2';
                            break;
                        case 'NOT_IN_DATABASE':
                            $strinValue = '3';
                        break;

                        default:
                            $strinValue = NULL;
                            break;
                    }
                    break;
                
                case 'VALUES_VERIFICATION':
                    switch ($value) {
                        case '0':
                            $strinValue = 'TRUE';
                            break;
                        case '1':
                            $strinValue = 'FALSE';
                        break;

                        default:
                            $strinValue = 'NONE';
                            break;
                    }
                    break;
                
                case 'VALUES_CURRENCY':
                    switch ($value) {
                        case 'Pesos Colombianos':
                            $strinValue = 'COP';
                            break;
                        case 'Dolares':
                            $strinValue = 'USD';
                        break;
                        case 'Bolivares':
                            $strinValue = 'BsF';
                        break;
                        default:
                            $strinValue = 'NONE';
                            break;
                    }
                    break;

                case 'VALUES_LANGUAGE':
                    switch ($value) {
                        case 'spanish':
                            $strinValue = 'es';
                            break;
                        case 'english':
                            $strinValue = 'en';
                        break;
                        default:
                            $strinValue = 'en';
                            break;
                    }
                    break;

                default:
                    $strinValue = 'NONE';
                    break;
            }

            return $strinValue;
        }

        // </utilities>

        // <functions>


        /**
        * Función responsable de retornar la cantidad de créditos disponibles
        * por el cliente.
        * @return float, cantidad de créditos disponibles por el cliente
        * @ created 26/FEB/2013
        * @author Sebastiaán Lara <slara@grvertice.com>
        */
        public function getCreditsClient() {
            return $this->cliCredit;
        }



        /**
        * Función responsable de consultar la información completa de un servicio
        * @param int $serviceId, identificador del servicio que se quiere obtener
        * la información.
        * @return Array $data, arreglo con los datos completos del servicio.
        * @created 12/FEB/2013
        */
        public function getDataService($serviceId) {
            $query = "SELECT s.ser_code, s.ser_req_country, s.ser_req_operator, "
                   . "s.ser_req_models, s.ser_req_MEP, s.ser_req_id, s.ser_req_user, "
                   . "s.ser_req_serie, s.ser_req_idbox, s.ser_req_nick, s.ser_req_pass, "
                   . "s.ser_req_imei, s.ser_req_rm, s.ser_price_purchase, s.ser_form_liberation, "
                   . "s.ser_algoritmo, st.type_service, "
                   . "(SELECT a.API_provider_code FROM api a "
                   . "WHERE a.MU_tool_code=" . (int) $serviceId . ") AS API_provider_code, "
                   . "(SELECT cp." . $this->columnPrice . " FROM client_price cp "
                   . "WHERE cp.ser_code=" . (int) $serviceId . " "
                   . "AND cp.cli_code=" . (int) $this->cliCode . ") AS " . $this->columnPrice . " "
                   . "FROM service_type st "
                   . "JOIN "
                   . "services s "
                   . "ON st.ser_type_code=s.ser_type_code "
                   . "WHERE s.ser_code=" . (int) $serviceId . ";";
            $responseList = NULL;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    $responseList['ser_code']            = $row['ser_code']; 
                    $responseList['ser_req_country']     = $row['ser_req_country'];
                    $responseList['ser_req_operator']    = $row['ser_req_operator'];
                    $responseList['ser_req_models']      = $row['ser_req_models'];
                    $responseList['ser_req_MEP']         = $row['ser_req_MEP'];
                    $responseList['ser_req_id']          = $row['ser_req_id'];
                    $responseList['ser_req_user']        = $row['ser_req_user'];
                    $responseList['ser_req_serie']       = $row['ser_req_serie'];
                    $responseList['ser_req_idbox']       = $row['ser_req_idbox'];
                    $responseList['ser_req_nick']        = $row['ser_req_nick'];
                    $responseList['ser_req_pass']        = $row['ser_req_pass'];
                    $responseList['ser_req_imei']        = $row['ser_req_imei'];
                    $responseList['ser_req_rm']          = $row['ser_req_rm'];
                    $responseList['ser_price_purchase']  = $row['ser_price_purchase'];
                    $responseList['ser_form_liberation'] = $row['ser_form_liberation'];
                    $responseList['ser_algoritmo']       = $row['ser_algoritmo'];
                    $responseList['API_provider_code']   = $row['API_provider_code'];
                    
                    $responseList['type_service']        = $row['type_service'];
                    $responseList['ser_price_client']    = $row[$this->columnPrice]; // precio en la moneda del cliente

                } else {
                    $responseList = self :: NO_DATA_FOUND;
                }
            } else {
                $responseList = self :: INTERNAL_ERROR;
            }

            return $responseList;
        }


        /**
        * Función responsable de consultar si una marca existe en la base de datos
        * de Master-Unlock.
        * @param int $brandId, marca a consultar.
        * @return boolean, retorna TRUE si la marca existe, FALSE en caso
        * contrario.
        * @created: 23/JAN/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateBrand($brandId) {
            $query = "SELECT COUNT(*) AS amount FROM brand b "
                   . "WHERE b.bra_code=" . (int) $brandId . ";";
            $isValid = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }

            return $isValid;
        }



        /**
        * Función responsable de consultar si un país existe en la base de datos
        * de Master-Unlock.
        * @param int $countryId, país a consultar.
        * @return boolean, retorna TRUE si el país existe, FALSE en caso
        * contrario.
        * @created: 23/JAN/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateCountry($countryId) {
            $query = "SELECT COUNT(*) AS amount FROM country c "
                   . "WHERE c.cou_code=" . (int) $countryId . ";";
            $isValid = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }

            return $isValid;
        }


        /**
        * Función responsable de consultar si un grupo existe en la base de datos
        * de Master-Unlock.
        * @param int $groupId, grupo a consultar.
        * @return boolean, retorna TRUE si el grupo existe, FALSE en caso
        * contrario.
        * @created: 24/JAN/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateGroup($groupId) {
            $query = "SELECT COUNT(*) AS amount FROM service_type st "
                   . "WHERE st.ser_type_code=" . (int) $groupId . ";";
            $isValid = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }

            return $isValid;
        }


        /**
        * Función responsable de consultar si un servicio existe en la base de datos
        * de Master-Unlock.
        * @param int $serviceId, servicio a consultar.
        * @return boolean, retorna TRUE si el servicio existe, FALSE en caso
        * contrario.
        * @created: 28/JAN/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateService($serviceId) {
            $query = "SELECT COUNT(*) AS amount FROM services s "
                   . "WHERE s.ser_code=" . (int) $serviceId . " AND "
                   . "s.ser_type_code IN "
                   . "(SELECT st.ser_type_code FROM service_type st "
                   . "WHERE st.type_service='Imei' OR st.type_service='Cable');"; 
            
            $isValid = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }

            return $isValid;
        }


        /**
        * Función responsable de consultar si una liberación existe o no.
        * @param int $orderId, id de la liberación a consultar.
        * @return boolean, retorna TRUE si la liberación existe, FALSE en caso
        * contrario.
        * @created: 31/JAN/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateOrderId($orderId) {
            $query = "SELECT COUNT(*) AS amount FROM liberations_imei li "
                   . "WHERE li.libimei_code=" . (int) $orderId . ";"; 
            
            $isValid = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }

            return $isValid;
        }


        /**
        * Función responsable de consultar si un modelo es o no válido. 
        * - Si se especifica el identificador de un servicio, se valida que el 
        * modelo sea soportado por el mismo.
        * - Si no se especifica el idenificador del servicio, se valida 
        * simplemente que el modelo exista.
        * @param int $mobileId, identificador del modelo a validar.
        * @param int $serviceId, identificador del servicio contra el que se
        * quiere validar el modelo.
        * @return boolean, retorna TRUE si el modelo es válido, FALSE en caso
        * contrario.
        * @created: 14/FEB/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateMobile($mobileId, $serviceId = NULL) {
            $query = "SELECT COUNT(*) AS amount FROM model m "
                   . "WHERE m.mod_code = " . (int) $mobileId . " ";
            $isValid      = FALSE;
            $messageError = self :: NO_DATA_FOUND;

            if (!is_null ($serviceId)) {
                $query .= "AND (SELECT COUNT(sm.semo_code) FROM services_model sm "
                       .  "WHERE sm.mod_code=" . $mobileId 
                       .  " AND sm.ser_code=" . (int) $serviceId . ");";
                $messageError = self :: NOT_SUPPORTED;
            }

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    } else {
                        $isValid = self :: NOT_SUPPORTED;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }
            return $isValid;
        }


        /**
        * Función responsable de consultar si una red es o no válida. 
        * @param int $networkId, identificador de la red a validar.
        * @return boolean, retorna TRUE si la red es válido, FALSE en caso
        * contrario.
        * @created: 21/FEB/2013
        * @author Sebastián Lara <slara@grvertice.com>
        */
        public function validateNetwork($networkId) {
            $query = "SELECT COUNT(*) AS amount FROM red r "
                   . "WHERE r.red_id = " . (int) $networkId . ";";
            $isValid = FALSE;

            if ($result = mysql_query ($query)) {
                if ($row = mysql_fetch_array ($result)) {
                    if ($row['amount'] == 1) {
                        $isValid = TRUE;
                    }
                } else {
                    $isValid = self :: NO_DATA_FOUND;
                }
            } else {
                $isValid = self :: INTERNAL_ERROR;
            }

            return $isValid;
        }
        // </functions>
    }